﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Runtime.InteropServices.JavaScript;
using System.Text;
using System.Threading.Tasks;

namespace 处理sql
{
    internal class PagingHelper
    {
        public static Tuple<int, DataTable> GetPagedDataAndTotalCount(string connectionString, string originalSql, int pageSize, int pageNumber, string dbType)
        {
            using (var connection = GetConnection(connectionString, dbType))
            {
                connection.Open();

                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        var totalRecordsSql = GetTotalCountSql(originalSql, dbType);
                        var totalRecordsCommand = GetCommand(totalRecordsSql, transaction, dbType);

                        int totalCount = Convert.ToInt32(totalRecordsCommand.ExecuteScalar());

                        var pagedSql = AddPagingToSql(originalSql, pageSize, pageNumber, dbType);
                        var pagedCommand = GetCommand(pagedSql, transaction, dbType);

                        var dataTable = new DataTable();
                        dataTable.Load(pagedCommand.ExecuteReader());

                        transaction.Commit();

                        return Tuple.Create(totalCount, dataTable);
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw new Exception($"Error processing SQL: {ex.Message}", ex);
                    }
                }
            }
        }

        private static string GetTotalCountSql(string originalSql, string dbType)
        {
            string selectPart = originalSql.Split(new[] { ' ' }, 2)[0];
            string fromPart = originalSql.Substring(originalSql.IndexOf("FROM", StringComparison.OrdinalIgnoreCase));

            if (selectPart.Equals("SELECT", StringComparison.OrdinalIgnoreCase))
            {
                return $"SELECT COUNT(*) FROM {fromPart}";
            }
            else if (selectPart.Equals("SELECT DISTINCT", StringComparison.OrdinalIgnoreCase))
            {
                return $"SELECT COUNT(DISTINCT *) FROM {fromPart}";
            }
            else
            {
                throw new ArgumentException("Unsupported SELECT statement.");
            }
        }

      

        private static IDbCommand GetCommand(string sql, IDbTransaction transaction, string dbType)
        {
            switch (dbType.ToLower())
            {
                case "sqlserver":
                    return new SqlCommand(sql, (SqlConnection)transaction.Connection, (SqlTransaction)transaction);
                case "mysql":
                    return new MySqlCommand(sql, (MySqlConnection)transaction.Connection, (MySqlTransaction)transaction);
                case "oracle":
                    return new OracleCommand(sql, (OracleConnection)transaction.Connection, (OracleTransaction)transaction);
                default:
                    throw new ArgumentException("Unsupported database type.");
            }
        }

        private static string AddPagingToSql(string originalSql,int pageSize,int pageNumber, string dbType)
        {
            return dbType switch
            {
                "sqlserver" => AddPagingToSqlServer(originalSql, pageSize, pageNumber),
                "mysql" => AddPagingToMysql(originalSql, pageSize, pageNumber),
                "oracle"=> AddPagingToOracle(originalSql, pageSize, pageNumber),
                _=>""
            };
        }

        private static string AddPagingToSqlServer(string originalSql, int pageSize, int pageNumber)
        {
            int skip = (pageNumber - 1) * pageSize;
            return $"{originalSql} OFFSET {skip} ROWS FETCH NEXT {pageSize} ROWS ONLY;";
        }

        private static string AddPagingToMysql(string originalSql, int pageSize, int pageNumber)
        {
            int skip = (pageNumber - 1) * pageSize;
            return $"{originalSql} LIMIT {pageSize} OFFSET {skip};";
        }

        private static string AddPagingToOracle(string originalSql, int pageSize, int pageNumber)
        {
            int skip = (pageNumber - 1) * pageSize;
            string selectPart = originalSql.Split(' ')[0];
            return $"SELECT * FROM ({originalSql}) WHERE ROWNUM <= {skip + pageSize} AND ROWNUM > {skip};";
        }
    }
}
